import sqlite3
import os


class DBManager(object):

    def __init__(self):
        pass

    def __enter__(self):
        home_path = os.path.expanduser('~')
        stock_db_path = os.path.join(home_path, '.stock', 'db')
        if not os.path.exists(stock_db_path):
            os.makedirs(stock_db_path)
        self.conn = sqlite3.connect(os.path.join(stock_db_path, 'stock.db'))
        return self.conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()
        return True

    def close(self):
        self.conn.close()


def get_conn():
    return DBManager()


def prepare_db():
    with get_conn() as conn:
        ddls = [
            "CREATE TABLE IF NOT EXISTS stock("
            "code varchar(10) primary key,"
            "market int not null,"
            "name varchar(30) not null"
            ");",
            "CREATE TABLE IF NOT EXISTS genes("
            "code varchar(10) primary key,"
            "market int not null,"
            "name varchar(30) not null"
            ");",
            "CREATE TABLE IF NOT EXISTS genes_kl("
            "id integer primary key autoincrement,"
            "code varchar(10) not null ,"
            "date varchar(12) not null ,"
            "open varchar(15) not null ,"
            "close varchar(15) not null ,"
            "low varchar(15) not null ,"
            "high varchar(15) not null ,"
            "total_volume int not null ,"
            "total_amount varchar(40) not null "
            ");",
            "CREATE TABLE IF NOT EXISTS stock_kl("
            "id integer primary key autoincrement,"
            "code varchar(10) not null ,"
            "date varchar(12) not null ,"
            "open varchar(15) not null ,"
            "close varchar(15) not null ,"
            "low varchar(15) not null ,"
            "high varchar(15) not null ,"
            "total_volume int not null ,"
            "total_amount varchar(40) not null"
            ");",
            "CREATE TABLE IF NOT EXISTS gene_stocks("
            "id integer primary key autoincrement,"
            "stock_code varchar(10) not null ,"
            "gene_code varchar(10) not null"
            ");",
            "create unique index if "
            "not exists gkl_code_date on genes_kl(code, date);",
            "create index if not exists gkl_date on genes_kl(date);",
            "create unique index if "
            "not exists skl_code_date on stock_kl(code, date);",
            "create index if not exists skl_date on stock_kl(date);",
            "create unique index if not exists gs_gs on gene_stocks(gene_code, stock_code)"
        ]
        for ddl in ddls:
            print(ddl)
            try:
                conn.execute(ddl)
            except Exception as e:
                print(e)


# prepare_db()


if __name__ == '__main__':
    pass
